import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:jyofservice/sql/Draft.dart';

class DatabaseHelper{
  static final DatabaseHelper _instance = new DatabaseHelper.internal();

  factory DatabaseHelper() => _instance;
  final String tableDraft = 'draftTable';
  final String lsh = 'lsh';
  final String dealTime = 'dealTime';
  final String dealResult = 'dealResult';
  final String dealResultType = 'dealResultType';
  final String dealContent = 'dealContent';
  final String location = 'location';
  final String longitude = 'longitude';
  final String latitude = 'latitude';
  final String imgeUrls = 'imgeUrls';
  final String imgeIds = 'imgeIds';
  final String fileArr = 'fileArr';
  final String delFileArr = 'delFileArr';
  final String doType = 'doType';
  final String doTypeName = 'doTypeName';
  final String assetsArr = 'assetsArr';

  static Database? _db;

  DatabaseHelper.internal();

  Future<Database?> get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDb();

    return _db;
  }

  initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'voiceservice2.db');

    var db = await openDatabase(path, version: 2, onCreate: _onCreate);
    return db;
  }

  void _onCreate(Database db, int newVersion) async {
    await db.execute(
        'CREATE TABLE $tableDraft($lsh TEXT PRIMARY KEY, $dealTime TEXT, $dealResult TEXT, $dealResultType TEXT,$dealContent TEXT,$location TEXT,$longitude TEXT,$latitude TEXT,$imgeUrls TEXT,$imgeIds TEXT,$fileArr TEXT,$delFileArr TEXT,$assetsArr TEXT,$doType TEXT,$doTypeName TEXT)');

  }

  Future<int?> insertVideo(DraftBean video) async {
    var dbClient = await db;
    var result = await dbClient?.insert(tableDraft, video.toJosn());

    return result;
  }

  Future<List> selectVideos({required int limit, required int offset}) async {
    var dbClient = await db;
    var result = await dbClient?.query(
      tableDraft,
      columns: [lsh, dealTime, dealResult,dealResultType,dealContent,location,longitude,latitude,imgeUrls,imgeIds,fileArr,delFileArr,doType,doTypeName,assetsArr],
      limit: limit,
      offset: offset,
    );
    List<DraftBean> videos = [];
    result?.forEach((item) => videos.add(DraftBean.fromSql(item)));
    return videos;
  }

  Future<int?> getCount() async {
    var dbClient = await db;
    if(dbClient == null){
      return 0;
    }
    return Sqflite.firstIntValue(
        await dbClient.rawQuery('SELECT COUNT(*) FROM $tableDraft'));
  }

  Future<DraftBean?> getVideo(String? Lsh) async {
    var dbClient = await db;
    if(dbClient != null) {
      List<Map> result = await dbClient.query(tableDraft,
          columns: [lsh, dealTime, dealResult,dealResultType,dealContent,location,longitude,latitude,imgeUrls,imgeIds,fileArr,delFileArr,doType,doTypeName,assetsArr],
          where: '$lsh = ?',
          whereArgs: [Lsh]);
      if (result.length > 0) {
        return DraftBean.fromSql(result.first as Map<String, dynamic>);
      }
    }
    return null;
  }

  Future<int?> deleteNote(String? LSH) async {
    var dbClient = await db;
    return await dbClient
        ?.delete(tableDraft, where: '$lsh = ?', whereArgs: [LSH]);
  }

  Future<int?> updateNote(DraftBean video) async {
    var dbClient = await db;
    return await dbClient?.update(tableDraft, video.toJosn(),
        where: "$lsh = ?", whereArgs: [video.lsh]);
  }

  Future close() async {
    var dbClient = await db;
    return dbClient?.close();
  }

}